using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using T2.WebSecurity.Data.Entities;
using T2.WebSecurity.Data.Entities.Interfaces;
using T2.WebSecurity.Data.Controllers.Interfaces;
using T2.WebSecurity.Data.Entities.Search.Interfaces;
using T2.WebSecurity.Data.Entities.Search;

namespace T2.WebSecurity.Data.Controllers
{
	public partial class MembershipController : IMembershipController
	{
	private IMembership TranslateMembershipFromDb(DataRow  _translateFrom)
	{
		IMembership temp = new Membership();
		temp.Action = T2.WebSecurity.Common.MyEnum.Actions.None.ToString();
		if(_translateFrom["MembershipID"] != null && _translateFrom["MembershipID"].ToString().Length > 0)
		{
		temp.MembershipID = Convert.ToInt32(_translateFrom["MembershipID"]);
		}
		else
		{
		temp.MembershipID = Convert.ToInt32("-1");
		}
		if(_translateFrom["ConfirmationToken"] != null && _translateFrom["ConfirmationToken"].ToString().Length > 0)
		{
		temp.ConfirmationToken = Convert.ToString(_translateFrom["ConfirmationToken"]);
		}
		else
		{
		temp.ConfirmationToken = String.Empty;
		}
		if(_translateFrom["IsConfirmed"] != null && _translateFrom["IsConfirmed"].ToString().Length > 0)
		{
		temp.IsConfirmed = Convert.ToBoolean(_translateFrom["IsConfirmed"]);
		}
		if(_translateFrom["LastPasswordFailureDate"] != null && _translateFrom["LastPasswordFailureDate"].ToString().Length > 0)
		{
		temp.LastPasswordFailureDate = Convert.ToDateTime(_translateFrom["LastPasswordFailureDate"]);
		}
		else
		{
		temp.LastPasswordFailureDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["PasswordFailuresSinceLastSuccess"] != null && _translateFrom["PasswordFailuresSinceLastSuccess"].ToString().Length > 0)
		{
		temp.PasswordFailuresSinceLastSuccess = Convert.ToInt32(_translateFrom["PasswordFailuresSinceLastSuccess"]);
		}
		else
		{
		temp.PasswordFailuresSinceLastSuccess = Convert.ToInt32("-1");
		}
		if(_translateFrom["Password"] != null && _translateFrom["Password"].ToString().Length > 0)
		{
		temp.Password = Convert.ToString(_translateFrom["Password"]);
		}
		else
		{
		temp.Password = String.Empty;
		}
		if(_translateFrom["PasswordChangedDate"] != null && _translateFrom["PasswordChangedDate"].ToString().Length > 0)
		{
		temp.PasswordChangedDate = Convert.ToDateTime(_translateFrom["PasswordChangedDate"]);
		}
		else
		{
		temp.PasswordChangedDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["PasswordSalt"] != null && _translateFrom["PasswordSalt"].ToString().Length > 0)
		{
		temp.PasswordSalt = Convert.ToString(_translateFrom["PasswordSalt"]);
		}
		else
		{
		temp.PasswordSalt = String.Empty;
		}
		if(_translateFrom["PasswordVerificationToken"] != null && _translateFrom["PasswordVerificationToken"].ToString().Length > 0)
		{
		temp.PasswordVerificationToken = Convert.ToString(_translateFrom["PasswordVerificationToken"]);
		}
		else
		{
		temp.PasswordVerificationToken = String.Empty;
		}
		if(_translateFrom["PasswordVerificationTokenExpirationDate"] != null && _translateFrom["PasswordVerificationTokenExpirationDate"].ToString().Length > 0)
		{
		temp.PasswordVerificationTokenExpirationDate = Convert.ToDateTime(_translateFrom["PasswordVerificationTokenExpirationDate"]);
		}
		else
		{
		temp.PasswordVerificationTokenExpirationDate = Convert.ToDateTime("01/01/1753");
		}
		temp.TS = (Byte[])_translateFrom["TS"];
		if(_translateFrom["CreatedBy"] != null && _translateFrom["CreatedBy"].ToString().Length > 0)
		{
		temp.CreatedBy = Convert.ToString(_translateFrom["CreatedBy"]);
		}
		else
		{
		temp.CreatedBy = String.Empty;
		}
		if(_translateFrom["CreatedDt"] != null && _translateFrom["CreatedDt"].ToString().Length > 0)
		{
		temp.CreatedDt = Convert.ToDateTime(_translateFrom["CreatedDt"]);
		}
		else
		{
		temp.CreatedDt = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["ModifiedBy"] != null && _translateFrom["ModifiedBy"].ToString().Length > 0)
		{
		temp.ModifiedBy = Convert.ToString(_translateFrom["ModifiedBy"]);
		}
		else
		{
		temp.ModifiedBy = String.Empty;
		}
		if(_translateFrom["ModifiedDt"] != null && _translateFrom["ModifiedDt"].ToString().Length > 0)
		{
		temp.ModifiedDt = Convert.ToDateTime(_translateFrom["ModifiedDt"]);
		}
		else
		{
		temp.ModifiedDt = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["EmailAccount"] != null && _translateFrom["EmailAccount"].ToString().Length > 0)
		{
		temp.EmailAccount = Convert.ToString(_translateFrom["EmailAccount"]);
		}
		else
		{
		temp.EmailAccount = String.Empty;
		}
		if(_translateFrom["PasswordAnswer"] != null && _translateFrom["PasswordAnswer"].ToString().Length > 0)
		{
		temp.PasswordAnswer = Convert.ToString(_translateFrom["PasswordAnswer"]);
		}
		else
		{
		temp.PasswordAnswer = String.Empty;
		}
		if(_translateFrom["LastLoginDate"] != null && _translateFrom["LastLoginDate"].ToString().Length > 0)
		{
		temp.LastLoginDate = Convert.ToDateTime(_translateFrom["LastLoginDate"]);
		}
		else
		{
		temp.LastLoginDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["LastLockoutDate"] != null && _translateFrom["LastLockoutDate"].ToString().Length > 0)
		{
		temp.LastLockoutDate = Convert.ToDateTime(_translateFrom["LastLockoutDate"]);
		}
		else
		{
		temp.LastLockoutDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["UserId"] != null && _translateFrom["UserId"].ToString().Length > 0)
		{
		temp.UserId = Convert.ToInt32(_translateFrom["UserId"]);
		}
		else
		{
		temp.UserId = Convert.ToInt32("-1");
		}
		if(_translateFrom["PasswordQuestionID"] != null && _translateFrom["PasswordQuestionID"].ToString().Length > 0)
		{
		temp.PasswordQuestionID = Convert.ToInt32(_translateFrom["PasswordQuestionID"]);
		}
		else
		{
		temp.PasswordQuestionID = Convert.ToInt32("-1");
		}
		return temp;
	}
	private IMembership TranslateSearchMembershipFromDb(DataRow  _translateFrom)
	{
		IMembership temp = new Membership();
		temp.Action = T2.WebSecurity.Common.MyEnum.Actions.None.ToString();
		if(_translateFrom["ConfirmationToken"] != null && _translateFrom["ConfirmationToken"].ToString().Length > 0)
		{
		temp.ConfirmationToken = Convert.ToString(_translateFrom["ConfirmationToken"]);
		}
		else
		{
		temp.ConfirmationToken = String.Empty;
		}
		if(_translateFrom["LastPasswordFailureDate"] != null && _translateFrom["LastPasswordFailureDate"].ToString().Length > 0)
		{
		temp.LastPasswordFailureDate = Convert.ToDateTime(_translateFrom["LastPasswordFailureDate"]);
		}
		else
		{
		temp.LastPasswordFailureDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["PasswordFailuresSinceLastSuccess"] != null && _translateFrom["PasswordFailuresSinceLastSuccess"].ToString().Length > 0)
		{
		temp.PasswordFailuresSinceLastSuccess = Convert.ToInt32(_translateFrom["PasswordFailuresSinceLastSuccess"]);
		}
		else
		{
		temp.PasswordFailuresSinceLastSuccess = Convert.ToInt32("-1");
		}
		if(_translateFrom["Password"] != null && _translateFrom["Password"].ToString().Length > 0)
		{
		temp.Password = Convert.ToString(_translateFrom["Password"]);
		}
		else
		{
		temp.Password = String.Empty;
		}
		if(_translateFrom["PasswordChangedDate"] != null && _translateFrom["PasswordChangedDate"].ToString().Length > 0)
		{
		temp.PasswordChangedDate = Convert.ToDateTime(_translateFrom["PasswordChangedDate"]);
		}
		else
		{
		temp.PasswordChangedDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["PasswordSalt"] != null && _translateFrom["PasswordSalt"].ToString().Length > 0)
		{
		temp.PasswordSalt = Convert.ToString(_translateFrom["PasswordSalt"]);
		}
		else
		{
		temp.PasswordSalt = String.Empty;
		}
		if(_translateFrom["PasswordVerificationToken"] != null && _translateFrom["PasswordVerificationToken"].ToString().Length > 0)
		{
		temp.PasswordVerificationToken = Convert.ToString(_translateFrom["PasswordVerificationToken"]);
		}
		else
		{
		temp.PasswordVerificationToken = String.Empty;
		}
		if(_translateFrom["PasswordVerificationTokenExpirationDate"] != null && _translateFrom["PasswordVerificationTokenExpirationDate"].ToString().Length > 0)
		{
		temp.PasswordVerificationTokenExpirationDate = Convert.ToDateTime(_translateFrom["PasswordVerificationTokenExpirationDate"]);
		}
		else
		{
		temp.PasswordVerificationTokenExpirationDate = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["EmailAccount"] != null && _translateFrom["EmailAccount"].ToString().Length > 0)
		{
		temp.EmailAccount = Convert.ToString(_translateFrom["EmailAccount"]);
		}
		else
		{
		temp.EmailAccount = String.Empty;
		}
		if(_translateFrom["PasswordAnswer"] != null && _translateFrom["PasswordAnswer"].ToString().Length > 0)
		{
		temp.PasswordAnswer = Convert.ToString(_translateFrom["PasswordAnswer"]);
		}
		else
		{
		temp.PasswordAnswer = String.Empty;
		}
		return temp;
	}
		public IDictionary<string,Int32> Membership_Add(IMembership  _membership)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_ins", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_MembershipID = DataHelpers.GetParameter("MembershipID", SqlDbType.Int, ParameterDirection.Output, 4) ;
			sp_MembershipID.Value = _membership.MembershipID;
			cmd.Parameters.Add(sp_MembershipID);

			SqlParameter sp_ConfirmationToken = DataHelpers.GetParameter("ConfirmationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_ConfirmationToken.Value = _membership.ConfirmationToken;
			cmd.Parameters.Add(sp_ConfirmationToken);

			SqlParameter sp_IsConfirmed = DataHelpers.GetParameter("IsConfirmed", SqlDbType.Bit, ParameterDirection.Input, 1) ;
			sp_IsConfirmed.Value = _membership.IsConfirmed;
			cmd.Parameters.Add(sp_IsConfirmed);

			SqlParameter sp_LastPasswordFailureDate = DataHelpers.GetParameter("LastPasswordFailureDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_LastPasswordFailureDate.Value = _membership.LastPasswordFailureDate;
			cmd.Parameters.Add(sp_LastPasswordFailureDate);

			SqlParameter sp_PasswordFailuresSinceLastSuccess = DataHelpers.GetParameter("PasswordFailuresSinceLastSuccess", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PasswordFailuresSinceLastSuccess.Value = _membership.PasswordFailuresSinceLastSuccess;
			cmd.Parameters.Add(sp_PasswordFailuresSinceLastSuccess);

			SqlParameter sp_Password = DataHelpers.GetParameter("Password", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_Password.Value = _membership.Password;
			cmd.Parameters.Add(sp_Password);

			SqlParameter sp_PasswordChangedDate = DataHelpers.GetParameter("PasswordChangedDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_PasswordChangedDate.Value = _membership.PasswordChangedDate;
			cmd.Parameters.Add(sp_PasswordChangedDate);

			SqlParameter sp_PasswordSalt = DataHelpers.GetParameter("PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordSalt.Value = _membership.PasswordSalt;
			cmd.Parameters.Add(sp_PasswordSalt);

			SqlParameter sp_PasswordVerificationToken = DataHelpers.GetParameter("PasswordVerificationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordVerificationToken.Value = _membership.PasswordVerificationToken;
			cmd.Parameters.Add(sp_PasswordVerificationToken);

			SqlParameter sp_PasswordVerificationTokenExpirationDate = DataHelpers.GetParameter("PasswordVerificationTokenExpirationDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_PasswordVerificationTokenExpirationDate.Value = _membership.PasswordVerificationTokenExpirationDate;
			cmd.Parameters.Add(sp_PasswordVerificationTokenExpirationDate);

			SqlParameter sp_CreatedBy = DataHelpers.GetParameter("CreatedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_CreatedBy.Value = _membership.CreatedBy;
			cmd.Parameters.Add(sp_CreatedBy);

			SqlParameter sp_CreatedDt = DataHelpers.GetParameter("CreatedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_CreatedDt.Value = _membership.CreatedDt;
			cmd.Parameters.Add(sp_CreatedDt);

			SqlParameter sp_ModifiedBy = DataHelpers.GetParameter("ModifiedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_ModifiedBy.Value = _membership.ModifiedBy;
			cmd.Parameters.Add(sp_ModifiedBy);

			SqlParameter sp_ModifiedDt = DataHelpers.GetParameter("ModifiedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_ModifiedDt.Value = _membership.ModifiedDt;
			cmd.Parameters.Add(sp_ModifiedDt);

			SqlParameter sp_EmailAccount = DataHelpers.GetParameter("EmailAccount", SqlDbType.NVarChar, ParameterDirection.Input, 255) ;
			sp_EmailAccount.Value = _membership.EmailAccount;
			cmd.Parameters.Add(sp_EmailAccount);

			SqlParameter sp_PasswordAnswer = DataHelpers.GetParameter("PasswordAnswer", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_PasswordAnswer.Value = _membership.PasswordAnswer;
			cmd.Parameters.Add(sp_PasswordAnswer);

			SqlParameter sp_LastLoginDate = DataHelpers.GetParameter("LastLoginDate", SqlDbType.DateTime, ParameterDirection.Input, 23) ;
			sp_LastLoginDate.Value = _membership.LastLoginDate;
			cmd.Parameters.Add(sp_LastLoginDate);

			SqlParameter sp_LastLockoutDate = DataHelpers.GetParameter("LastLockoutDate", SqlDbType.DateTime, ParameterDirection.Input, 23) ;
			sp_LastLockoutDate.Value = _membership.LastLockoutDate;
			cmd.Parameters.Add(sp_LastLockoutDate);

			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _membership.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_PasswordQuestionID = DataHelpers.GetParameter("PasswordQuestionID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PasswordQuestionID.Value = _membership.PasswordQuestionID;
			cmd.Parameters.Add(sp_PasswordQuestionID);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);

			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			int PkMembershipID = Convert.ToInt32(sp_MembershipID.Value);
			KeyValuePair<string, int> PKMembershipID = new KeyValuePair<string, int>("PKMembershipID", PkMembershipID);
			RetDict.Add(PKMembershipID);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			}
			}
		}
		public IDictionary<string,Int32> Membership_Modify(IMembership  _membership)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_udt", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_MembershipID = DataHelpers.GetParameter("MembershipID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_MembershipID.Value = _membership.MembershipID;
			cmd.Parameters.Add(sp_MembershipID);

			SqlParameter sp_ConfirmationToken = DataHelpers.GetParameter("ConfirmationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_ConfirmationToken.Value = _membership.ConfirmationToken;
			cmd.Parameters.Add(sp_ConfirmationToken);

			SqlParameter sp_IsConfirmed = DataHelpers.GetParameter("IsConfirmed", SqlDbType.Bit, ParameterDirection.Input, 1) ;
			sp_IsConfirmed.Value = _membership.IsConfirmed;
			cmd.Parameters.Add(sp_IsConfirmed);

			SqlParameter sp_LastPasswordFailureDate = DataHelpers.GetParameter("LastPasswordFailureDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_LastPasswordFailureDate.Value = _membership.LastPasswordFailureDate;
			cmd.Parameters.Add(sp_LastPasswordFailureDate);

			SqlParameter sp_PasswordFailuresSinceLastSuccess = DataHelpers.GetParameter("PasswordFailuresSinceLastSuccess", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PasswordFailuresSinceLastSuccess.Value = _membership.PasswordFailuresSinceLastSuccess;
			cmd.Parameters.Add(sp_PasswordFailuresSinceLastSuccess);

			SqlParameter sp_Password = DataHelpers.GetParameter("Password", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_Password.Value = _membership.Password;
			cmd.Parameters.Add(sp_Password);

			SqlParameter sp_PasswordChangedDate = DataHelpers.GetParameter("PasswordChangedDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_PasswordChangedDate.Value = _membership.PasswordChangedDate;
			cmd.Parameters.Add(sp_PasswordChangedDate);

			SqlParameter sp_PasswordSalt = DataHelpers.GetParameter("PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordSalt.Value = _membership.PasswordSalt;
			cmd.Parameters.Add(sp_PasswordSalt);

			SqlParameter sp_PasswordVerificationToken = DataHelpers.GetParameter("PasswordVerificationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordVerificationToken.Value = _membership.PasswordVerificationToken;
			cmd.Parameters.Add(sp_PasswordVerificationToken);

			SqlParameter sp_PasswordVerificationTokenExpirationDate = DataHelpers.GetParameter("PasswordVerificationTokenExpirationDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_PasswordVerificationTokenExpirationDate.Value = _membership.PasswordVerificationTokenExpirationDate;
			cmd.Parameters.Add(sp_PasswordVerificationTokenExpirationDate);

			SqlParameter sp_TS = DataHelpers.GetParameter("TS", SqlDbType.Timestamp, ParameterDirection.Input, 8) ;
			sp_TS.Value = _membership.TS;
			cmd.Parameters.Add(sp_TS);

			SqlParameter sp_CreatedBy = DataHelpers.GetParameter("CreatedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_CreatedBy.Value = _membership.CreatedBy;
			cmd.Parameters.Add(sp_CreatedBy);

			SqlParameter sp_CreatedDt = DataHelpers.GetParameter("CreatedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_CreatedDt.Value = _membership.CreatedDt;
			cmd.Parameters.Add(sp_CreatedDt);

			SqlParameter sp_ModifiedBy = DataHelpers.GetParameter("ModifiedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_ModifiedBy.Value = _membership.ModifiedBy;
			cmd.Parameters.Add(sp_ModifiedBy);

			SqlParameter sp_ModifiedDt = DataHelpers.GetParameter("ModifiedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_ModifiedDt.Value = _membership.ModifiedDt;
			cmd.Parameters.Add(sp_ModifiedDt);

			SqlParameter sp_EmailAccount = DataHelpers.GetParameter("EmailAccount", SqlDbType.NVarChar, ParameterDirection.Input, 255) ;
			sp_EmailAccount.Value = _membership.EmailAccount;
			cmd.Parameters.Add(sp_EmailAccount);

			SqlParameter sp_PasswordAnswer = DataHelpers.GetParameter("PasswordAnswer", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_PasswordAnswer.Value = _membership.PasswordAnswer;
			cmd.Parameters.Add(sp_PasswordAnswer);

			SqlParameter sp_LastLoginDate = DataHelpers.GetParameter("LastLoginDate", SqlDbType.DateTime, ParameterDirection.Input, 23) ;
			sp_LastLoginDate.Value = _membership.LastLoginDate;
			cmd.Parameters.Add(sp_LastLoginDate);

			SqlParameter sp_LastLockoutDate = DataHelpers.GetParameter("LastLockoutDate", SqlDbType.DateTime, ParameterDirection.Input, 23) ;
			sp_LastLockoutDate.Value = _membership.LastLockoutDate;
			cmd.Parameters.Add(sp_LastLockoutDate);

			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _membership.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_PasswordQuestionID = DataHelpers.GetParameter("PasswordQuestionID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PasswordQuestionID.Value = _membership.PasswordQuestionID;
			cmd.Parameters.Add(sp_PasswordQuestionID);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			int PkMembershipID = Convert.ToInt32(sp_MembershipID.Value);
			KeyValuePair<string, int> PKMembershipID = new KeyValuePair<string, int>("PKMembershipID", PkMembershipID);
			RetDict.Add(PKMembershipID);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			}
			}
		}
		public IDictionary<string, int> Membership_Remove(IMembership  _membership)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_del", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_MembershipID = DataHelpers.GetParameter("MembershipID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_MembershipID.Value = _membership.MembershipID;
			cmd.Parameters.Add(sp_MembershipID);

			SqlParameter sp_TS = DataHelpers.GetParameter("TS", SqlDbType.Timestamp, ParameterDirection.Input, 8) ;
			sp_TS.Value = _membership.TS;
			cmd.Parameters.Add(sp_TS);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public IMembership Membership_GetOne(IMembership  _membership)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_GetOne", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_MembershipID = DataHelpers.GetParameter("MembershipID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_MembershipID.Value = _membership.MembershipID;
			cmd.Parameters.Add(sp_MembershipID);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			IMembership RetVal = new Membership();
			if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
			RetVal = TranslateMembershipFromDb(ds.Tables[0].Rows[0]);
			RetVal.TotalRows = Affected;
			}
			return RetVal;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IMembership> Membership_GetAll(IMembership  _membership)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_GetAll", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _membership.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _membership.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _membership.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _membership.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IMembership> tempList = new List<IMembership>();
			
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
                    IMembership temp = TranslateMembershipFromDb(r);
					temp.TotalRows = Convert.ToInt32(sp_TotalRows.Value);
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IMembership> Membership_Search(IMembershipSearch _membership)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("Membership_Search", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_ConfirmationToken = DataHelpers.GetParameter("ConfirmationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_ConfirmationToken.Value = _membership.ConfirmationToken;
			cmd.Parameters.Add(sp_ConfirmationToken);

			SqlParameter sp_Beg_LastPasswordFailureDate = DataHelpers.GetParameter("Beg_LastPasswordFailureDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_Beg_LastPasswordFailureDate.Value = _membership.Beg_LastPasswordFailureDate;
			cmd.Parameters.Add(sp_Beg_LastPasswordFailureDate);

			SqlParameter sp_End_LastPasswordFailureDate = DataHelpers.GetParameter("End_LastPasswordFailureDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_End_LastPasswordFailureDate.Value = _membership.End_LastPasswordFailureDate;
			cmd.Parameters.Add(sp_End_LastPasswordFailureDate);

			SqlParameter sp_Beg_PasswordFailuresSinceLastSuccess = DataHelpers.GetParameter("Beg_PasswordFailuresSinceLastSuccess", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_Beg_PasswordFailuresSinceLastSuccess.Value = _membership.Beg_PasswordFailuresSinceLastSuccess;
			cmd.Parameters.Add(sp_Beg_PasswordFailuresSinceLastSuccess);

			SqlParameter sp_End_PasswordFailuresSinceLastSuccess = DataHelpers.GetParameter("End_PasswordFailuresSinceLastSuccess", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_End_PasswordFailuresSinceLastSuccess.Value = _membership.End_PasswordFailuresSinceLastSuccess;
			cmd.Parameters.Add(sp_End_PasswordFailuresSinceLastSuccess);

			SqlParameter sp_Password = DataHelpers.GetParameter("Password", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_Password.Value = _membership.Password;
			cmd.Parameters.Add(sp_Password);

			SqlParameter sp_Beg_PasswordChangedDate = DataHelpers.GetParameter("Beg_PasswordChangedDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_Beg_PasswordChangedDate.Value = _membership.Beg_PasswordChangedDate;
			cmd.Parameters.Add(sp_Beg_PasswordChangedDate);

			SqlParameter sp_End_PasswordChangedDate = DataHelpers.GetParameter("End_PasswordChangedDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_End_PasswordChangedDate.Value = _membership.End_PasswordChangedDate;
			cmd.Parameters.Add(sp_End_PasswordChangedDate);

			SqlParameter sp_PasswordSalt = DataHelpers.GetParameter("PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordSalt.Value = _membership.PasswordSalt;
			cmd.Parameters.Add(sp_PasswordSalt);

			SqlParameter sp_PasswordVerificationToken = DataHelpers.GetParameter("PasswordVerificationToken", SqlDbType.NVarChar, ParameterDirection.Input, 128) ;
			sp_PasswordVerificationToken.Value = _membership.PasswordVerificationToken;
			cmd.Parameters.Add(sp_PasswordVerificationToken);

			SqlParameter sp_Beg_PasswordVerificationTokenExpirationDate = DataHelpers.GetParameter("Beg_PasswordVerificationTokenExpirationDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_Beg_PasswordVerificationTokenExpirationDate.Value = _membership.Beg_PasswordVerificationTokenExpirationDate;
			cmd.Parameters.Add(sp_Beg_PasswordVerificationTokenExpirationDate);

			SqlParameter sp_End_PasswordVerificationTokenExpirationDate = DataHelpers.GetParameter("End_PasswordVerificationTokenExpirationDate", SqlDbType.DateTime, ParameterDirection.Input, 8) ;
			sp_End_PasswordVerificationTokenExpirationDate.Value = _membership.End_PasswordVerificationTokenExpirationDate;
			cmd.Parameters.Add(sp_End_PasswordVerificationTokenExpirationDate);

			SqlParameter sp_EmailAccount = DataHelpers.GetParameter("EmailAccount", SqlDbType.NVarChar, ParameterDirection.Input, 255) ;
			sp_EmailAccount.Value = _membership.EmailAccount;
			cmd.Parameters.Add(sp_EmailAccount);

			SqlParameter sp_PasswordAnswer = DataHelpers.GetParameter("PasswordAnswer", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_PasswordAnswer.Value = _membership.PasswordAnswer;
			cmd.Parameters.Add(sp_PasswordAnswer);

			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _membership.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _membership.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _membership.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _membership.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IMembership> tempList = new List<IMembership>();
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
					IMembership temp = new Membership();
					temp = TranslateMembershipFromDb(r);
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IMembership> UserProfile_Membership_ByParent(IMembership _membership)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_dbo_Membership_ByParent", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _membership.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _membership.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _membership.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _membership.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _membership.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			//int Affected = Convert.ToInt32(sp_RowsAfected.Value);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IMembership> tempList = new List<IMembership>();
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
					IMembership temp = new Membership();
					temp.TotalRows = Convert.ToInt32(sp_TotalRows.Value);
					temp = TranslateMembershipFromDb(r);
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IMembership> PasswordQuestion_Membership_ByParent(IMembership _membership)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("PasswordQuestion_dbo_Membership_ByParent", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_PasswordQuestionID = DataHelpers.GetParameter("PasswordQuestionID", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PasswordQuestionID.Value = _membership.PasswordQuestionID;
			cmd.Parameters.Add(sp_PasswordQuestionID);

			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _membership.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _membership.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _membership.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _membership.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			//int Affected = Convert.ToInt32(sp_RowsAfected.Value);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IMembership> tempList = new List<IMembership>();
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
					IMembership temp = new Membership();
					temp.TotalRows = Convert.ToInt32(sp_TotalRows.Value);
					temp = TranslateMembershipFromDb(r);
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}

    }
}
